using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Xml.XPath;
using System.Data;
using System.Data.Common;

namespace SMS.Common.Data
{
    //public class SqlQueryStringService
    //{
    //    ///<summary>
    //    /// Get Sql string and replace parameters which defined in xml statement with args[].
    //    /// 
    //    /// For example: 
    //    /// <?xml version="1.0" encoding="utf-8" ?>
    //    /// <SqlQueryStringService>
    //    ///   <Sql module="QueryStringModule" name="SqlExample" description="A Sql statement sample.">
    //    ///       <Statement>
    //    ///       <![CDATA[
    //    ///         select * from mytable where mycolumn = <%para1%>
    //    ///       ]]>
    //    ///       </Statement>
    //    ///       <Para name="para1" />
    //    ///   </Sql>
    //    /// </SqlQueryStringService>
    //    /// We have defined a parameter named "para1", when we using GetSqlString method,
    //    /// we must provide a string which include in args[], for example,
    //    /// 
    //    /// string[] myArgs = new string[1];
    //    /// myArgs.SetValue("'myvalue'", 0);
    //    /// 
    //    /// after GetSqlString, the Sql String will be:
    //    /// 
    //    /// select * from mytable where mycolumn = 'myvalue'
    //    /// 
    //    /// We can also define para in the statement with "@", for example, "@paraname",
    //    /// and then, you can set the para value at run time with SqlParameters.Value property.
    //    /// for example:
    //    /// 
    //    /// select * from mytable where mycolumn = @myvalue
    //    /// 
    //    /// in this satuation, you don't need provide a "<Para>" section in XML file, 
    //    /// and you also don't need provide a value string in args[] for the "@" parameter.
    //    ///</summary>
    //    public static string GetSqlString(string sqlQueryStringFileUri, string sqlModule, string sqlName, string[] args)
    //    {
    //        string tmpSql = "";
    //        XmlDocument doc = new XmlDocument();
    //        try
    //        {
    //            doc.Load(sqlQueryStringFileUri);
    //            XmlNode node = doc.DocumentElement.SelectSingleNode(@"//Sql[@module='" + sqlModule + @"' and @name='" + sqlName + @"']");
    //            XmlNode cdataNode = node.SelectSingleNode("./Statement");
    //            XmlCDataSection cdata = (XmlCDataSection)cdataNode.ChildNodes[0];
    //            tmpSql = cdata.Data;
    //            XmlNodeList nodes = node.SelectNodes(@"./Para");
    //            if (nodes.Count > 0)
    //                return TrimSqlStatement(ReplaceParas(tmpSql, nodes, args));
    //            else
    //                return TrimSqlStatement(tmpSql);
    //        }
    //        catch (XmlException e)
    //        {
    //            return "";
    //        }
    //    }

    //    ///<summary>
    //    /// Get Sql string without replace any para, even there are para declared in xml file.
    //    ///</summary>
    //    public static string GetSqlString(string sqlQueryStringFileUri, string sqlModule, string sqlName)
    //    {
    //        return GetSqlString(sqlQueryStringFileUri, sqlModule, sqlName, null);
    //    }

    //    private static string ReplaceParas(string tmpSql, XmlNodeList nodes, string[] args)
    //    {
    //        string tmpString = tmpSql;
    //        try
    //        {
    //            for (int i = 0; i < nodes.Count; i++)
    //            {
    //                XmlNode node = nodes[i];
    //                tmpString = tmpString.Replace("<%" + node.Attributes["name"].Value + "%>", args[i]);
    //            }
    //            return tmpString;
    //        }
    //        catch
    //        {
    //            return "";
    //        }
    //    }

    //    private static string TrimSqlStatement(string sql)
    //    {
    //        return sql.Replace("\n", " ").Replace("\t", " ").Replace("\r", " ").Trim();
    //    }
    //}

    public class SqlQueryStringService
    {
        ///<summary>
        /// Get Sql string and replace para (which defined in xml statement) with args[].
        /// Notition: This function will not replace "''" out of para which is declared in xml file. 
        /// So, if you want to using args[] to replace para, be care of data type, 
        /// you must add "''" out of para which is declared in xml file if data type is string, datetime, char, etc.
        /// you can also define para without declare in xml file, 
        /// and then you can set the para value at runtime with SqlParameter.Value property.
        ///</summary>
        public static string GetSqlString(string xmlfileName,string sqlModule, string sqlName, string[] args)
        {
            string tmpSql = "";
            XmlDocument doc = new XmlDocument();
            try
            {
                doc.Load(xmlfileName);
                XmlNode node = doc.DocumentElement.SelectSingleNode(@"//Sql[@module='" + sqlModule + @"' and @name='" + sqlName + @"']");
                XmlNode cdataNode = node.SelectSingleNode("./Statement");
                XmlCDataSection cdata = (XmlCDataSection)cdataNode.ChildNodes[0];
                tmpSql = cdata.Data;
                XmlNodeList nodes = node.SelectNodes(@"./Para");
                if (nodes.Count > 0)
                    return TrimSqlStatement(ReplaceParas(tmpSql, nodes, args));
                else
                    return TrimSqlStatement(tmpSql);
            }
            catch (XmlException e)
            {
                return "";
            }
        }

        ///<summary>
        /// Get Sql string without replace any para, even there are para declared in xml file.
        /// You can define para in the statement with "@", for example, "@paraname",
        /// and then, you can set the para value at run time with SqlParameters.Value property.
        /// Important: you must not add "''" out of your para in xml file in this satuation, it is different from GetSqlString().
        ///</summary>
        public static string GetSqlString(string sqlModule, string sqlName)
        {
            string tmpSql = "";
            XmlDocument doc = new XmlDocument();
            try
            {
                doc.Load(AppDomain.CurrentDomain.BaseDirectory + "DataService\\SqlService.xml");
                XmlNode node = doc.DocumentElement.SelectSingleNode(@"//Sql[@module='" + sqlModule + @"' and @name='" + sqlName + @"']");
                XmlNode cdataNode = node.SelectSingleNode("./Statement");
                XmlCDataSection cdata = (XmlCDataSection)cdataNode.ChildNodes[0];
                tmpSql = cdata.Data;
                return TrimSqlStatement(tmpSql);
            }
            catch (XmlException e)
            {
                return "";
            }
        }

        private static string ReplaceParas(string tmpSql, XmlNodeList nodes, string[] args)
        {
            string tmpString = tmpSql;
            try
            {
                for (int i = 0; i < nodes.Count; i++)
                {
                    XmlNode node = nodes[i];
                    tmpString = tmpString.Replace(node.Attributes["name"].Value, args[i]);
                }
                return tmpString;
            }
            catch
            {
                return "";
            }
        }

        private static string TrimSqlStatement(string sql)
        {
            return sql.Replace("\n", " ").Replace("\t", " ").Replace("\r", " ").Trim();
        }
    }
}
